<?php
class ajax_sqlinc{
	
	public function execute(){
	
		$operFlg = isset($_REQUEST['operFlg']) ? $_REQUEST['operFlg'] : null;
	
		switch ($operFlg){
			case 1:
				$this->init();//初始化
				break;
			case 2:
				$this->getTable();//查询
				break;
			case 3:
				$this->getSql();//修改、新增权限
				break;
// 			case 4:
// 				$this->delete();//删除
// 				break;
		}
	
	}
	
	/* 
	 * 初始化表格
	 *  */
	private function init(){
		
		global $G_PGLOB;
		
		$data = $G_PGLOB->DBCA->stmt_query('show tables');
		
		$dbname = 'Tables_in_'.G_DB_DATABASE;
		
		$result_arr = array();
		
		$tableName = isset($_REQUEST['tableSelect']) ? trim($_REQUEST['tableSelect']) : $data[0]->$dbname;
		
		try{	
			$sql = "SHOW FULL FIELDS FROM "."`".G_DB_DATABASE."`.`".$tableName."`";
			$result = $G_PGLOB->DBCA->stmt_query($sql);
			if(!$result){
		
				$result_arr['code'] = 500;
				$result_arr['message'] = '未查到'.$tableName.'表明细';
			
			}else {
			
				$result_arr['code'] = 200;
				$result_arr['page'] = isset($_REQUEST['page']) ? $_REQUEST['page'] : 1;
				$result_arr['total'] = count($result);
				$result_arr['rows'] = array();
			
			
				//遍历获得的数据
				foreach ($result as $v)
				{
					$result_arr['rows'][] = array(
							'id' =>$v->Field,
							'cell' => array(
									$v->Field,
									$v->Type,
									$v->Key,
									$v->Comment
							)
					);
						
				}
				//$html.='</table>';
			
			}
		
			
			}
			
		catch(Exception $e)
			{
			error_log($e->getMessage());
			$result_arr['code'] = 1000;
			$result_arr['message'] = $tableName.'无此表';
			}
			
			echo json_encode($result_arr);
	}
	
	/* 
	 * 查询表名
	 *  */
	private function getTable(){
		
		global $G_PGLOB;
		
		$screenName = isset($_REQUEST['screenName']) ? trim($_REQUEST['screenName']) : null;
		
		try
		{
			$result = $G_PGLOB->DBCA->stmt_query("select table_name from information_schema.tables where table_name like '%".$screenName."%' and table_schema = '".G_DB_DATABASE."'");
			$opstion ='';
			if(empty($result)){
				
				$opstion.='<option>无此表</option><br>';
				
			}else {
				
				foreach ($result as $tb)
				{
					$opstion.='<option>'.$tb->table_name.'</option><br>';
				}
			}
			
			echo json_encode($opstion);
		}
		catch(Exception $e)
		{
			error_log($e->getMessage());
			
		}
		
	}
	
	/* 
	 * 生成SQL语句的入口
	 *  */
	private function getSql(){
		global $G_PGLOB;
		
		$result_arr = array();
		
// 		if (!isset($_REQUEST['tableName']) || empty($_REQUEST['tableName']) || !isset($_REQUEST['flg']) || !is_numeric($_REQUEST['flg'])){
			
// 			$result_arr['code'] = 400;
// 			$result_arr['message'] = '传值错误';
			
// 		}else {
			
// 			$tableName = trim($_REQUEST['tableName']);
			$tableName = 'bill_add';
			$flg = 1;
// 			$flg = trim($_REQUEST['flg']);
			
			$result = $this->sqlPut($tableName, $flg);
// 			error_log(print_r($result,true));
			if (isset($result) && !empty($result)){
				
				$result_arr['code'] = 500;
				$result_arr['message'] = $result;
				
			}else {
				
				$result_arr['code'] = 200;
				$result_arr['message'] = 'SQL语句生成成功';
				
			}
// 		}
		
		echo json_encode($result_arr);
	}
	
	/* 
	 * 生成SQL语句
	 *  */
	function sqlPut($tableName,$operFlg)
	{
		global  $G_PGLOB;
		if(empty($tableName))
		{
			return;
		}
		$insertValue ='';
		$updateValue ='';
		$updateValue_Name ='';
		$updateWhere ='';
		$insertSql = 'INSERT INTO '.$tableName.'(';
		$updateSql = 'UPDATE '.$tableName.' SET ';
		$insertType = '\'';
		$updateType = '\'';
		$updateTypeEnd ='';
		$type =null;
		try
		{
			$result = $G_PGLOB->DBCA->stmt_query("SHOW FULL FIELDS FROM "."`".G_DB_DATABASE."`.`".$tableName."`");
			$sql = "SHOW FULL FIELDS FROM "."`".G_DB_DATABASE."`.`".$tableName."`";
			if(empty($result))
			{
				$G_PGLOB->smarty->assign('msg','查询表明细出错');
				$G_PGLOB->smarty->display('project/showTable.tpl');
			}
			foreach ($result as $v)
			{
				//获得字符串类型去掉“（）”
				$type =trim($v->Type);
				if(strpos($type,'('))
				{
					$type =substr($type,0, strpos($type,'('));
				}
				else
				{
					$type =trim($v->Type);
				}
				//拼接表明细html
				//$html.='<tr><td>'.$v->Field.'</td><td>'.$v->Type.'</td><td>'.$v->Null.'</td><td>'.$v->Key.'</td><td>'.$v->Default.'</td><td>'.$v->Extra.'</td></tr>';
				//update,判断是否为主键拼接WHERE条件
				if($v->Key=='PRI')
				{
					//如果是主键第一次出现
					if($updateWhere=='')
					{
						$updateWhere.=' where '.$v->Field.'=? ';
						$updateValue_Name.=$v->Field;
					}
					//主键不是第二次出现拼接AND条件
					else
					{
						$updateWhere.=' and '.$v->Field.'=? ';
						$updateValue_Name.=' ,'.$v->Field;
					}
					//判断字段类型
					switch($type)
					{
						case 'smallint' :
							$updateTypeEnd.='i';
							break;
						case 'mediumint' :
							$updateTypeEnd.='i';
							break;
						case 'double' :
							$updateTypeEnd.='d';
							break;
						case 'float' :
							$updateTypeEnd.='f';
							break;
						case 'tinyint' :
							$updateTypeEnd.='i';
							break;
						case 'int' :
							$updateTypeEnd.='i';
							break;
						case 'decimal' :
							$updateTypeEnd.='d';
							break;
						case 'bigint' :
							$updateTypeEnd.='i';
							break;
							break;
						default:
							$updateTypeEnd.='s';
							break;
					}
				}
				//如果不是主键
				else
				{
					//拼接update语句
					$updateSql.=$v->Field.'=?,';
					//拼接update语句对应值
					$updateValue.=$v->Field.', ';
					//判断字段类型
					switch($type)
					{
						case 'smallint' :
							$updateType.='i';
							break;
						case 'mediumint' :
							$updateType.='i';
							break;
						case 'double' :
							$updateType.='d';
							break;
						case 'float' :
							$updateType.='f';
							break;
						case 'tinyint' :
							$updateType.='i';
							break;
						case 'int' :
							$updateType.='i';
							break;
						case 'decimal' :
							$updateType.='d';
							break;
						case 'bigint' :
							$updateType.='i';
							break;
						default:
							$updateType.='s';
							break;
					}
				}
				//拼接insert语句
				$insertSql.=$v->Field.',';
				//拼接insert语句值对应的列名
				$insertValue.=$v->Field.',';
				//拼接类型
				//判断字段类型
				switch($type)
				{
					case 'smallint' :
						$insertType.='i';
						break;
					case 'mediumint' :
						$insertType.='i';
						break;
					case 'double' :
						$insertType.='d';
						break;
					case 'float' :
						$insertType.='f';
						break;
					case 'tinyint' :
						$insertType.='i';
						break;
					case 'int' :
						$insertType.='i';
						break;
					case 'decimal' :
						$insertType.='d';
						break;
					case 'bigint' :
						$insertType.='i';
						break;
					default:
						$insertType.='s';
						break;
				}
			}
			//$html.='</table>';
			//拼接insert语句
			$insertSql=substr($insertSql,0,strlen($insertSql)-1).') VALUES (';
			foreach ($result as $t)
			{
				$insertSql.='?,';
			}
			$insertSql='$_INC_SQL =\''.substr($insertSql,0,strlen($insertSql)-1).')\';'."\r\n".'$_INC_SQL_PARAMS_TYPE ='.substr($insertType,0,strlen($insertType)).'\';'."\r\n".'$_INC_SQL_PARAMS_VALUE =\''.substr($insertValue,0,strlen($insertValue)-1).'\';'."\r\n";
			//拼接update语句
			$updateSql='$_INC_SQL =\''.substr($updateSql,0,strlen($updateSql)-1).$updateWhere.'\';'."\r\n".'$_INC_SQL_PARAMS_TYPE ='.$updateType.$updateTypeEnd.'\';'."\r\n".'$_INC_SQL_PARAMS_VALUE =\''.$updateValue.$updateValue_Name.'\';'."\r\n";
			//最后返回
			//拼接组成数组
			$inc_begin = "<?php\r\n";
			$inc_end ='?>';
			//如果是3则生成insert语句下载
			if($operFlg=='1')
			{
				$insertfile = $inc_begin.$insertSql.$inc_end;
				$encod=mb_detect_encoding($insertfile);
				if($encod != "UTF-8"){
					$insertfile	= iconv($encod,"UTF-8//IGNORE",$insertfile);
				}
				//文档名称
				$insert_File_Name =$tableName.".insert.inc";
				$file_name = $insert_File_Name ;
				header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
				header('Expires:0');
				header('Pragma:public');
				header("Content-type: text/inc");
				header("Content-Disposition: attachment; filename=" . $file_name);
				header("Content-Length: " . strlen($insertfile));
				echo $insertfile;
				exit;
			}
			//生成update语句下载
			$updatefile = $inc_begin.$updateSql.$inc_end;
			$encod=mb_detect_encoding($updatefile);
			if($encod != "UTF-8"){
				$updatefile	= iconv($encod,"UTF-8//IGNORE",$updatefile);
			}
			//文档名称
			$update_File_Name =$tableName.".update.inc";
			$file_name = $update_File_Name ;
			header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
			header('Expires:0');
			header('Pragma:public');
			header("Content-type: text/csv");
			header("Content-Disposition: attachment; filename=" . $file_name);
			header("Content-Length: " . strlen($updatefile));
			echo $updatefile;
			exit;
		}
		catch(Exception $e)
		{
			error_log($e->getMessage());
			return $e->getMessage();
			
		}
	}
}